package controller;

import entity.Data;
import entity.User;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * Author:shixiaojun@itany.com
 * Date:2022/3/11 15:54
 * Version:1.0-SNAPSHOT
 */
@Controller
@RequestMapping("/ajax")
public class AjaxController {

    @ResponseBody
    @RequestMapping(value = "/checkUsername",produces = "text/html;charset=utf-8")
    public String checkUsername(String username){
//        response.setContentType("text/html;charset=utf-8");
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String url = "jdbc:mysql://127.0.0.1:3306/ums?useUnicode=true&characterEncoding=utf-8";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,"root","");
            String sql = new StringBuffer()
                    .append(" select id,username,password,phone,address ")
                    .append(" from t_user ")
                    .append(" where username = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1,username);
            rs = ps.executeQuery();
            if(rs.next()){
                return "<span style='color:red;'>该用户名已经存在</span>";
            }
            return "<span style='color:green;'>用户名可用</span>";
        } catch (Exception e) {
            e.printStackTrace();
            return "<span style='color:red;'>服务器内部异常</span>";
        }
    }

    @ResponseBody
    @RequestMapping("/findUser")
    public User findUser() throws ParseException {
        User user = new User(1,"admin","123456","13812345678","江苏-南京");
        user.setBirthday(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("1999-09-09 08:00:00"));
        return user;
    }

    @RequestMapping("/findAll")
    public ModelAndView findAll(){
        ModelAndView mav = new ModelAndView();

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String url = "jdbc:mysql://127.0.0.1:3306/ums?useUnicode=true&characterEncoding=utf-8";
        List<User> users = new ArrayList<>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,"root","");
            String sql = new StringBuffer()
                    .append(" select id,username,password,phone,address ")
                    .append(" from t_user ")
                    .toString();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                users.add(user);
            }
            mav.addObject("users",users);
            mav.setViewName("list");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return mav;
    }

    @ResponseBody
    @RequestMapping("/findById")
    public User findById(Integer id){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String url = "jdbc:mysql://127.0.0.1:3306/ums?useUnicode=true&characterEncoding=utf-8";
        User user = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,"root","");
            String sql = new StringBuffer()
                    .append(" select id,username,password,phone,address ")
                    .append(" from t_user ")
                    .append(" where id = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            if(rs.next()){
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return user;
    }

    @RequestMapping("/showQuery")
    public String showQuery(){
        return "query";
    }

    @ResponseBody
    @RequestMapping("/query")
    public List<Data> query(String spell){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String url = "jdbc:mysql://127.0.0.1:3306/ums?useUnicode=true&characterEncoding=utf-8";
        List<Data> datas = new ArrayList<>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,"root","");
            String sql = new StringBuffer()
                    .append(" select id,spell,message ")
                    .append(" from t_data ")
                    .append(" where spell like ? ")
                    .append(" or message like ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1,"%"+spell+"%");
            ps.setString(2,"%"+spell+"%");
            rs = ps.executeQuery();
            while(rs.next()){
                Data data = new Data();
                data.setId(rs.getInt("id"));
                data.setSpell(rs.getString("spell"));
                data.setMessage(rs.getString("message"));
                datas.add(data);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return datas;
    }

}
